﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySqlConnector;
using System.Data;
using System.Data.Common;
using System.IO;
using CRL.DBAccess;
using CRL;
using System.Data.SqlClient;

namespace CRL.MySql
{
    partial class MySqlHelper : DBHelper
    {
		public MySqlHelper(DBAccessBuild dBAccessBuild)
            : base(dBAccessBuild)
        { }
        public override DBType CurrentDBType
        {
            get
            {
                return DBType.MYSQL;
            }
        }
        
        protected override DbDataAdapter CreateDa(string cmdText, IDbConnection conn)
        {
            return new MySqlDataAdapter(cmdText, (MySqlConnection)conn);
        }
        protected override IDbConnection CreateConn()
        {

            return new MySqlConnection(ConnectionString);
        }

        public override void InsertFromDataTable(DataTable dataTable, string tableName, bool keepIdentity = false)
        {
            //批量写入问题
            //https://blog.csdn.net/weixin_35440082/article/details/113424636

            MySqlBulkCopy sqlBulkCopy;
            if (_trans != null)
            {
                var sqlTrans = _trans as MySqlTransaction;
                sqlBulkCopy = new MySqlBulkCopy(currentConn as MySqlConnection, sqlTrans);
            }
            else
            {
                GetOrCreateConn();
                sqlBulkCopy = new MySqlBulkCopy(currentConn as MySqlConnection, null);
            }
            sqlBulkCopy.DestinationTableName = tableName;
            //sqlBulkCopy.BatchSize = dataTable.Rows.Count;
            if (dataTable != null && dataTable.Rows.Count != 0)
            {
                sqlBulkCopy.WriteToServer(dataTable);
            }
            //sqlBulkCopy.Close();
        }
        //public override void InsertFromDataTable(DataTable table, string tableName, bool keepIdentity = false)
        //{
        //    if (table.Rows.Count == 0) return;
        //    string tmpPath = Path.GetTempFileName();
        //    string csv = DataTableToCsv(table);
        //    File.WriteAllText(tmpPath, csv);
        //    using (var conn = currentConn ?? CreateConn())
        //    {
        //        try
        //        {
        //            MySqlBulkLoader bulk = new MySqlBulkLoader(conn as MySqlConnection)
        //            {
        //                FieldTerminator = ",",
        //                FieldQuotationCharacter = '"',
        //                EscapeCharacter = '"',
        //                LineTerminator = "\r\n",
        //                FileName = tmpPath,
        //                NumberOfLinesToSkip = 0,
        //                TableName = tableName,
        //                Local = true,
        //            };
        //            //bulk.Columns.AddRange(table.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToArray());
        //            var insertCount = bulk.Load();
        //        }
        //        catch (MySqlException ex)
        //        {
        //            throw ex;
        //        }
        //    }
        //    File.Delete(tmpPath);
        //}

        private static string DataTableToCsv(DataTable table)
        {
            //以半角逗号（即,）作分隔符，列为空也要表达其存在。
            //列内容如存在半角逗号（即,）则用半角引号（即""）将该字段值包含起来。
            //列内容如存在半角引号（即"）则应替换成半角双引号（""）转义，并用半角引号（即""）将该字段值包含起来。
            StringBuilder sb = new StringBuilder();
            DataColumn colum;
            foreach (DataRow row in table.Rows)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    colum = table.Columns[i];
                    if (i != 0) sb.Append(",");
                    if (colum.DataType == typeof(string) && row[colum].ToString().Contains(","))
                    {
                        sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\"");
                    }
                    else sb.Append(row[colum].ToString());
                }
                sb.AppendLine();
            }
            return sb.ToString();
        }
    }

}
